package com.rocksdata.mc.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
	public DatabaseHelper(Context context, String dataBaseName, int dataBaseVersion){
		super(context, dataBaseName, null, dataBaseVersion);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		TApplicationInfo.createTable(db);
		TUserInfo.createTable(db);
//		TTimetableInfo.createTable(db);
        TAnnouncementInfo.createTable(db);
//		TPayrollInfo.createTable(db);
//		TTranscriptInfo.createTable(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		TApplicationInfo.upgradeTable(db);
		TUserInfo.upgradeTable(db);
//		TTimetableInfo.upgradeTable(db);
        TAnnouncementInfo.upgradeTable(db);
		onCreate(db);
	}

	public static class TApplicationInfo{
		public final static String TABLE_NAME = "ApplicationInfo";
		public final static String APP_CODE = "appCode";
		public final static String APP_NAME = "appName";
		public final static String APP_ICON = "appIcon";
		public final static String APP_GROUP = "appGroup";
		public final static String APP_USABLE = "appUsable";

		private static void createTable(SQLiteDatabase db){
			String sqlCreateTable = "CREATE TABLE IF NOT EXISTS "
					+ TABLE_NAME
					+ "("
					+ APP_CODE + " TEXT,"
					+ APP_NAME + " TEXT,"
					+ APP_ICON + " TEXT,"
					+ APP_GROUP + " TEXT,"
					+ APP_USABLE + " INTEGER"
					+ ")";
			db.execSQL(sqlCreateTable);
//			String indexSql = "CREATE UNIQUE INDEX " + APP_ID + " ON " + TABLE_NAME + "(" + APP_ID + ")";
//			db.execSQL(indexSql);
		}

		private static void upgradeTable(SQLiteDatabase db){
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		}
	}
	
	public static class TUserInfo{
		public final static String TABLE_NAME = "UserInfo";
		public final static String USER_ID = "userId";
		public final static String USER_NAME = "userName";
		public final static String USER_TYPE = "userType";
		public final static String PARENT_ID = "parentId";
		public final static String PARENT_NAME = "parentName";
		public final static String HEAD_URL = "avatar";
		public final static String MOBILE = "mobile";
		public final static String QR_CODE = "qrcode";
		public final static String HOME_ADDRESS = "homeAddress";
		public final static String EMAIL = "email";
		public final static String HOME_TEL = "homeTel";
		public final static String JOB_TITLE = "jobTitle";
		public final static String OFFICE = "office";
		public final static String OFFICE_TEL = "officeTel";
		public final static String HEADMASTER_ID = "headmasterId";
		public final static String HEADMASTER_NAME = "headmasterName";
		public final static String HEADMASTER_PHONE = "headmasterPhone";
		public final static String GUARDIAN_NAME = "guardianName";
		public final static String GUARDIAN_PHONE = "guardianPhone";
		public final static String BEDROOM_ID = "bedroomId";
		public final static String BEDROOM_NAME = "bedroomName";
		public final static String BEDROOM_ADDRESS = "bedroomAddress";

		private static void createTable(SQLiteDatabase db){
			String sqlCreateTable = "CREATE TABLE IF NOT EXISTS "
					+ TABLE_NAME
					+ "("
					+ USER_ID + " TEXT,"
					+ USER_NAME + " TEXT,"
					+ USER_TYPE + " INTEGER,"
					+ PARENT_ID + " TEXT,"
					+ PARENT_NAME + " TEXT,"
					+ HEAD_URL + " TEXT,"
					+ MOBILE + " TEXT,"
					+ QR_CODE + " TEXT,"
					+ HOME_ADDRESS + " TEXT,"
					+ EMAIL + " TEXT,"
					+ HOME_TEL + " TEXT,"
					+ JOB_TITLE + " TEXT,"
					+ OFFICE + " TEXT,"
					+ OFFICE_TEL + " TEXT,"
					+ HEADMASTER_ID + " TEXT,"
					+ HEADMASTER_NAME + " TEXT,"
					+ HEADMASTER_PHONE + " TEXT,"
					+ GUARDIAN_NAME + " TEXT,"
					+ GUARDIAN_PHONE + " TEXT,"
					+ BEDROOM_ID + " TEXT,"
					+ BEDROOM_NAME + " TEXT,"
					+ BEDROOM_ADDRESS + " TEXT"
					+ ")";
			db.execSQL(sqlCreateTable);
			String indexSql = "CREATE UNIQUE INDEX " + USER_ID + " ON " + TABLE_NAME + "(" + USER_ID + ")";
			db.execSQL(indexSql);
		}
		
		private static void upgradeTable(SQLiteDatabase db){
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		}
	}

	public static class TTimetableInfo{
		public final static String TABLE_NAME = "TimetableInfo";
		public final static String COURSE_ID = "courseId";
		public final static String DAY_OF_WEEK = "dayOfWeek";
		public final static String COURSE_NAME = "courseName";
		public final static String COURSE_INDEX = "courseIndex";
		public final static String TEACHER_ID = "teacherId";
		public final static String TEACHER_NAME = "teacherName";
		public final static String LOCATION = "location";
		public final static String ZONE = "zone";
		public final static String STATUS = "status";
		public final static String SUBSTITUTE_COURSE_ID = "substituteCourseId";
		public final static String SUBSTITUTE_COURSE_NAME = "substituteCourseName";
		public final static String SUBSTITUTE_TEACHER_ID = "substituteTeacherId";
		public final static String SUBSTITUTE_TEACHER_NAME = "substituteTeacherName";
		public final static String SUBSTITUTE_LOCATION = "substituteLocation";

		private static void createTable(SQLiteDatabase db){
			String sqlCreateTable = "CREATE TABLE IF NOT EXISTS "
					+ TABLE_NAME
					+ "("
					+ COURSE_ID + " TEXT,"
					+ DAY_OF_WEEK + " INTEGER,"
					+ COURSE_NAME + " TEXT,"
					+ COURSE_INDEX + " INTEGER,"
					+ TEACHER_ID + " TEXT,"
					+ TEACHER_NAME + " TEXT,"
					+ LOCATION + " TEXT,"
					+ ZONE + " INTEGER,"
					+ STATUS + " INTEGER DEFAULT 0,"
					+ SUBSTITUTE_COURSE_ID + " TEXT,"
					+ SUBSTITUTE_COURSE_NAME + " TEXT,"
					+ SUBSTITUTE_TEACHER_ID + " TEXT,"
					+ SUBSTITUTE_TEACHER_NAME + " TEXT,"
					+ SUBSTITUTE_LOCATION + " TEXT"
					+ ")";
			db.execSQL(sqlCreateTable);
			String indexSql = "CREATE UNIQUE INDEX " + COURSE_ID + " ON " + TABLE_NAME + "(" + COURSE_ID + ")";
			db.execSQL(indexSql);
		}

		private static void upgradeTable(SQLiteDatabase db){
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		}
	}

    public static class TAnnouncementInfo {
        public final static String TABLE_NAME = "AnnouncementInfo";
        public final static String ANNOUNCEMENT_ID = "announcementId";
        public final static String READ_STATUS = "readStatus";
        public final static String MARK_STATUS = "markStatus";
        public final static String TO_TOP_DATE = "toTopDate";

        private static void createTable(SQLiteDatabase db){
            String sqlCreateTable = "CREATE TABLE IF NOT EXISTS "
                    + TABLE_NAME
                    + "("
                    + ANNOUNCEMENT_ID + " TEXT,"
                    + READ_STATUS + " INTEGER DEFAULT 0,"
                    + MARK_STATUS + " INTEGER DEFAULT 0,"
                    + TO_TOP_DATE + " INTEGER"
                    + ")";
            db.execSQL(sqlCreateTable);
            String indexSql = "CREATE UNIQUE INDEX " + ANNOUNCEMENT_ID + " ON " + TABLE_NAME + "(" + ANNOUNCEMENT_ID + ")";
            db.execSQL(indexSql);
        }

        private static void upgradeTable(SQLiteDatabase db){
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        }
    }

	public static class TPayrollInfo{
		public final static String TABLE_NAME = "PayrollInfo";
		public final static String YEAR_MONTH = "yearMonth";
		public final static String JSON_DATA = "jsonData";

		private static void createTable(SQLiteDatabase db){
			String sqlCreateTable = "CREATE TABLE IF NOT EXISTS "
					+ TABLE_NAME
					+ "("
					+ YEAR_MONTH + " TEXT,"
					+ JSON_DATA + " TEXT"
					+ ")";
			db.execSQL(sqlCreateTable);
		}

		private static void upgradeTable(SQLiteDatabase db){
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		}
	}

	public static class TTranscriptInfo{
		public final static String TABLE_NAME = "TranscriptInfo";
		public final static String DATE_MILLIS = "dateMillis";
		public final static String JSON_DATA = "jsonData";

		private static void createTable(SQLiteDatabase db){
			String sqlCreateTable = "CREATE TABLE IF NOT EXISTS "
					+ TABLE_NAME
					+ "("
					+ DATE_MILLIS + " INTEGER,"
					+ JSON_DATA + " TEXT"
					+ ")";
			db.execSQL(sqlCreateTable);
		}

		private static void upgradeTable(SQLiteDatabase db){
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		}
	}
}
